﻿#region Copyright(C) 2018 FI Ltd. All rights reserved.
// ==================================================================================================
// Copyright(C) 2018 FI Ltd. All rights reserved.
//
// SYSTEM NAME	    : FI.Net Application Framework
// COMPONENT ID	    : FI.Core
// COMPONENT DESC   : FI.Net Core Foundation
//
// CREATED DATE/BY  : 18 Aug 2018 / Tian Mi
//
// REVISION HISTORY :	
// DATE/BY			SR#/CS/PM#/OTHERS		DESCRIPTION OF CHANGE
// ==================================================================================================
#endregion

using System.Data;
using System.Data.Common;

namespace FI.Core.Data
{
    /// <summary>
    /// A abstract database which is used to perform basic data access operation.
    /// </summary>
    public abstract class Database
    {
        /// <summary>
        /// Gets the name of the database.
        /// </summary>
        /// <value>
        /// The name of the database.
        /// </value>
        public abstract string DatabaseName { get; }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns>The number of rows affected.</returns>
        public abstract int ExecuteNonQuery(DbCommand command, DbTransaction transaction = null);

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure.</param>
        /// <param name="parameterValues">The parameter values.</param>
        /// <returns>The number of rows affected.</returns>
        public abstract int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues);

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns>The number of rows affected.</returns>
        public abstract int ExecuteNonQuery(CommandType commandType, string commandText);

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="transaction">The transaction.</param>
        /// <param name="storedProcedureName">Name of the stored procedure.</param>
        /// <param name="parameterValues">The parameter values.</param>
        /// <returns>The number of rows affected.</returns>
        public abstract int ExecuteNonQuery(DbTransaction transaction, string storedProcedureName, params object[] parameterValues);

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="transaction">The transaction.</param>
        public abstract IDataReader ExecuteReader(DbCommand command, DbTransaction transaction = null);

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure.</param>
        /// <param name="parameterValues">The parameter values.</param>
        /// <returns></returns>
        public abstract IDataReader ExecuteReader(string storedProcedureName,params object[] parameterValues);


        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public abstract IDataReader ExecuteReader(CommandType commandType, string commandText);

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public abstract object ExecuteScalar(CommandType commandType, string commandText);

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <returns></returns>
        public abstract object ExecuteScalar(DbCommand command);

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns></returns>
        public abstract object ExecuteScalar(DbCommand command, DbTransaction transaction);

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns>The <see cref="DataSet"/>.</returns>
        public abstract DataSet ExecuteDataSet(DbCommand command, DbTransaction transaction = null);

        /// <summary>
        /// Gets the stored proc command.
        /// </summary>
        /// <param name="procedureName">Name of the procedure.</param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Proc")]
        public abstract DbCommand GetStoredProcCommand(string procedureName);

        /// <summary>
        /// Gets the stored proc command.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure.</param>
        /// <param name="parameterValues">The parameter values.</param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Proc")]
        public abstract DbCommand GetStoredProcCommand(string storedProcedureName, params object[] parameterValues);

        /// <summary>
        /// Gets the database provider factory.
        /// </summary>
        /// <value>
        /// The database provider factory.
        /// </value>
        public abstract DbProviderFactory DbProviderFactory { get; }

        /// <summary>
        /// Discovers the parameters.
        /// </summary>
        /// <param name="cmd">The command.</param>
        public abstract void DiscoverParameters(DbCommand cmd);
       

        /// <summary>
        /// Creates the connection.
        /// </summary>
        /// <returns></returns>
        public abstract DbConnection CreateConnection();

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object to the command.</para>
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>        
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>  
        public abstract void AddParameter(DbCommand command, string name, DbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value);

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the in parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <remarks>
        /// <para>This version of the method is used when you can have the same parameter object multiple times with different values.</para>
        /// </remarks>  
        public abstract void AddInParameter(DbCommand command, string name, DbType dbType);

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <param name="value"><para>The value of the parameter.</para></param>    
        public abstract void AddInParameter(DbCommand command, string name, DbType dbType, object value);

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the value.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        public abstract void AddInParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion);

        /// <summary>
        /// Adds the out parameter.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="name">The name.</param>
        /// <param name="dbType">Type of the db.</param>
        /// <param name="size">The size.</param>
        public abstract void AddOutParameter(DbCommand command, string name, DbType dbType, int size);

        /// <summary>
        /// Gets the parameter value.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public abstract object GetParameterValue(DbCommand command, string name);


        /// <summary>
        /// Sets the parameter value.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="name">The name.</param>
        /// <param name="value">The value.</param>
        public abstract void SetParameterValue(DbCommand command, string name, object value);


        /// <summary>
        /// Loads the data set.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="tableName">Name of the table.</param>
        public abstract void LoadDataSet(DbCommand command, DataSet dataSet, string tableName);

        /// <summary>
        /// Loads the data set.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="transaction">The transaction.</param>
        public abstract void LoadDataSet(DbCommand command, DataSet dataSet, string tableName, DbTransaction transaction);

        /// <summary>
        /// Loads the data set.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="tableNames">The table names.</param>
        public abstract void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames);

        /// <summary>
        /// Loads the data set.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="dataSet">The data set.</param>
        /// <param name="tableNames">The table names.</param>
        /// <param name="transaction">The transaction.</param>
        public abstract void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames, DbTransaction transaction);

        
        /// <summary>
        /// <para>Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the <see cref="DataSet"/> within a transaction.</para>
        /// </summary>        
        /// <param name="dataSet"><para>The <see cref="DataSet"/> used to update the data source.</para></param>
        /// <param name="tableName"><para>The name of the source table to use for table mapping.</para></param>
        /// <param name="insertCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Added"/>.</para></param>
        /// <param name="updateCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Modified"/>.</para></param>        
        /// <param name="deleteCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Deleted"/>.</para></param>        
        /// <param name="transaction"><para>The <see cref="IDbTransaction"/> to use.</para></param>
        /// <returns>Number of records affected.</returns>        
        public abstract int UpdateDataSet(DataSet dataSet, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, DbTransaction transaction);

        /// <summary>
        /// <para>Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the <see cref="DataSet"/>.</para>
        /// </summary>        
        /// <param name="dataSet"><para>The <see cref="DataSet"/> used to update the data source.</para></param>
        /// <param name="tableName"><para>The name of the source table to use for table mapping.</para></param>
        /// <param name="insertCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Added"/></para></param>
        /// <param name="updateCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Modified"/></para></param>        
        /// <param name="deleteCommand"><para>The <see cref="DbCommand"/> executed when <see cref="DataRowState"/> is <seealso cref="DataRowState.Deleted"/></para></param>  
        /// <param name="updateBehavior"><para>One of the <see cref="UpdateBehavior"/> values.</para></param> 
        /// <returns>number of records affected</returns>        
        public abstract int UpdateDataSet(DataSet dataSet, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, UpdateBehavior updateBehavior);
        
        /// <summary>
        /// Gets a DbDataAdapter with Standard update behavior.
        /// </summary>
        /// <returns>A <see cref="DbDataAdapter"/>.</returns>
        /// <seealso cref="DbDataAdapter"/>
        /// <devdoc>
        /// Created this new, public method instead of modifying the protected, abstract one so that there will be no
        /// breaking changes for any currently derived Database class.
        /// </devdoc>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public abstract DbDataAdapter GetDataAdapter();

        /// <summary>
        /// Wraps around a derived class's implementation of the GetStoredProcCommandWrapper method and adds functionality for
        /// using this method with UpdateDataSet.  The GetStoredProcCommandWrapper method (above) that takes a params array 
        /// expects the array to be filled with VALUES for the parameters. This method differs from the GetStoredProcCommandWrapper 
        /// method in that it allows a user to pass in a string array. It will also dynamically discover the parameters for the 
        /// stored procedure and set the parameter's SourceColumns to the strings that are passed in. It does this by mapping 
        /// the parameters to the strings IN ORDER. Thus, order is very important.
        /// </summary>
        /// <param name="storedProcedureName"><para>The name of the stored procedure.</para></param>
        /// <param name="sourceColumns"><para>The list of DataFields for the procedure.</para></param>
        /// <returns><para>The <see cref="DbCommand"/> for the stored procedure.</para></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Proc")]
        public abstract DbCommand GetStoredProcCommandWithSourceColumns(string storedProcedureName, params string[] sourceColumns);

        /// <summary>
        /// Builds DBMS specific parameter name.
        /// </summary>
        /// <param name="parameterName">The parameter name without any prefix symbol.</param>
        /// <returns>The DBMS specific parameter name.</returns>
        public abstract string BuildParameterName(string parameterName);

        /// <summary>
        /// Build a <see cref="DbCommand"/> based on the SQL text.
        /// </summary>
        /// <param name="query">The SQL text.</param>
        /// <returns>The <see cref="DbCommand"/>.</returns>
        public abstract DbCommand GetSqlStringCommand(string query);

        /// <summary>
        /// Gets a db connection that will be not be disposed if a transaction is active (created by creating a System.TransactionScope instance). 
        /// The connection will be disposed when no transaction is active.
        /// </summary>
        /// <returns>The connection what state is Open.</returns>
        public abstract DbConnection GetOpenConnection();
    }
}
